<?php

namespace App\Console\Commands;

use App\Library\Logger;
use App\Models\CityModel;
use Illuminate\Console\Command;
use PhpOffice\PhpSpreadsheet\IOFactory;

class ImportTwHkMacaoZones extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'import:thm_zones';

    private $logger;

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = '导入港澳台数据';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
        $this->logger = new Logger('import/thm_zones');
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $bench = new \Ubench();
        $bench->start();

        $this->importData();

        $bench->end();
        $this->logger->debug("time used: " . $bench->getTime());
        $this->logger->debug("memory used: " . $bench->getMemoryUsage());
    }

    private function importData()
    {
        $hkZone = CityModel::query()->where('name', 'like', '%香港%')->where('pid', '=', 0)->first();
        $hkId = $hkZone->id;
        $this->importDbData($hkId, 'hk', "香港");

        $twZone = CityModel::query()->where('name', 'like', '%台湾%')->where('pid', '=', 0)->first();
        $twId = $twZone->id;
        $this->importDbData($twId, 'tw', "台湾");

        $macaoZone = CityModel::query()->where('name', 'like', '%澳门%')->where('pid', '=', 0)->first();
        $macaoId = $macaoZone->id;
        $this->importDbData($macaoId, 'macao', "澳门");
    }



    private function importDbData($id, $key, $title)
    {
        $allData = $this->getImportData();
        $hkData = $allData[$key] ?? [];
        if (!$hkData) {
            $this->logger->info("导入{$title}数据失败，excel中不含{$title}数据...");
            return;
        }

        // 1级 城市: pid = 0
        foreach ($hkData as $item) {
            $maxCitycode = intval(CityModel::query()->max('citycode'));
            $currentMaxCityCode = $maxCitycode + 1;
            $itemData = [
                'name' => $item['name'],
                'citycode' => $currentMaxCityCode
            ];
            if ($item['pid'] == 0) {
                $pid = $id;

                $exist = CityModel::query()
                    ->where('pid', $pid)
                    ->where('name', $itemData['name'])
                    ->count();
                if ($exist) {
                    $this->logger->info("导入{$title}数据, 数据已存在，跳过: " . $itemData['name']);
                    continue;
                }

                $this->logger->info("导入{$title}数据: " . $itemData['name']);

                $level = 2;
                $itemData['pid'] = $pid;
                $itemData['level'] = $level;
                $city = new CityModel();
                $city->fill($itemData);
                $city->saveOrFail();
            } else {
                $parentName = $item['parent_name'];
                $parentCity = CityModel::query()->where('name', $parentName)->where('pid', '>', 0)->first();
                $pid = $parentCity->id ?? 0;
                $level = 3;
                $exist = CityModel::query()
                    ->where('pid', $pid)
                    ->where('name', $itemData['name'])
                    ->count();
                if ($exist) {
                    $this->logger->info("导入{$title}数据, 数据已存在，跳过: " . $itemData['name']);
                    continue;
                }

                $this->logger->info("导入{$title}数据: " . $itemData['name']);

                $itemData['pid'] = $pid;
                $itemData['level'] = $level;
                $city = new CityModel();
                $city->fill($itemData);
                $city->saveOrFail();
            }
        }
    }

    // 从excel中读取数据
    private function getImportData()
    {
        $this->logger->info("读取港澳台数据");

        $hk = $tw = $macao = [];

        $file = public_path('resources/zones/') . 'hk_tw_macao.xlsx';
        if (!is_file($file)) {
            $this->logger->error("港澳台数据不存在");
            return [
                'hk' => $hk,
                'tw' => $tw,
                'macao' => $macao
            ];
        }
        $reader = IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(true);
        $spreadsheet = $reader->load($file);
        $worksheet = $spreadsheet->getActiveSheet();
        $rowCount = $worksheet->getHighestRow();
        for ($i = 2; $i <= $rowCount; $i++) {
            $provinceName = trim($worksheet->getCellByColumnAndRow(5, $i)->getValue() ?? '');
            $item = [
                'name' => trim($worksheet->getCellByColumnAndRow(1, $i)->getValue() ?? ''),
                'parent_name' => trim($worksheet->getCellByColumnAndRow(2, $i)->getValue() ?? ''),
                'id' => intval($worksheet->getCellByColumnAndRow(3, $i)->getValue() ?? ''),
                'pid' => intval($worksheet->getCellByColumnAndRow(4, $i)->getValue() ?? '')
            ];
            if (strrpos($provinceName, '香港') !== false) {
                $hk[] = $item;
            }
            if (strrpos($provinceName, '澳门') !== false) {
                $macao[] = $item;
            }
            if (strrpos($provinceName, '台湾') !== false) {
                $tw[] = $item;
            }
        }

        $result = [
            'hk' => $hk,
            'tw' => $tw,
            'macao' => $macao
        ];
        return $result;
    }
}
